﻿CREATE FUNCTION [sp_ssiscatalog].[executions]
(
		@exec_id					BIGINT			=	NULL						/*Specify which execution we want to return information for*/
,		@folder_name				SYSNAME			=	NULL
,		@project_name				SYSNAME			=	NULL
,		@package_name				SYSNAME			=	NULL
,		@executed_as_name			SYSNAME			=	NULL
,		@execs_status_desc			NVARCHAR(MAX)	=	NULL
,		@execs_minimum_execution_id	BIGINT			=	NULL
,		@execs_maximum_execution_id	BIGINT			=	NULL
)
RETURNS TABLE
AS
RETURN
		WITH cte AS (
				SELECT	[ResultSet]=N'EXECUTION',e.[folder_name],e.[project_name],e.[package_name],e.[executed_as_name],sd.[status_desc],e.[start_time],e.[end_time],duration_in_seconds=DATEDIFF(ss,e.[start_time],ISNULL(e.[end_time],SYSDATETIMEOFFSET())),e.[execution_id]
				FROM	[$(SSISDB)].[catalog].[executions]e
				CROSS APPLY [sp_ssiscatalog].[get_status_desc_from_status](e.[status]) sd
				WHERE	(	@exec_id IS NULL
						OR	e.[execution_id] = @exec_id
						)
					AND	(
							@folder_name IS NULL
						OR	e.[folder_name] =  @folder_name                 
						)
					AND	(
							@project_name IS NULL
						OR	e.[project_name] =  @project_name                 
						)
					AND	(
							@package_name IS NULL
						OR	e.[package_name] =  @package_name                 
						)
					AND	(
							@executed_as_name IS NULL
						OR	e.[executed_as_name] =  @executed_as_name                 
						)
					AND	(
							@execs_status_desc IS NULL
						OR	sd.[status_desc] =  @execs_status_desc                 
						)
					AND	(
							@execs_minimum_execution_id IS NULL
						OR	e.[execution_id] >=  @execs_minimum_execution_id
						)
					AND	(
							@execs_maximum_execution_id IS NULL
						OR	e.[execution_id] <=  @execs_maximum_execution_id
						)
		)
		SELECT	c.[ResultSet]
		,		c.[folder_name]
		,		c.[project_name]
		,		c.[package_name]
		,		c.[executed_as_name]
		,		c.[status_desc]
		,		c.[start_time]
		,		c.[end_time]
		,		c.[duration_in_seconds]
		,		c.[execution_id]
		,		[relative_duration] = sp_ssiscatalog.bars(ROUND((CONVERT(DECIMAL(12,0),c.[duration_in_seconds]) / CONVERT(DECIMAL(12,0),m_c.[max_duration])) * 100,0))
		FROM	cte c
		CROSS JOIN (
				SELECT	max_duration=MAX(c.[duration_in_seconds])
				FROM	cte c
		)m_c
		--; 2012-11-13, semi-colon needs to be commented out due to a bug in DacFx that causes a null reference exception when deploying this function. The DacFx product team are aware and are hopefully fixing it.